package com.yanzhang.dao.provider;

import com.mysql.cj.util.StringUtils;
import com.yanzhang.pojo.Provider;
import com.yanzhang.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @author yanzhang
 */
public class ProviderDaoImpl implements ProviderDao{
    /**
     * 增加一个供应商
     * @param connection 数据库连接
     * @param provider 供应商对象
     * @return 结果数量
     * @throws Exception 抛出SQL异常
     */
    @Override
    public int add(Connection connection, Provider provider) throws Exception {
        int result = 0;
        PreparedStatement preparedStatement = null;
        if(null != connection && provider != null){
            String sql = "insert into smbms_provider (proCode,proName,proDesc,proContact,proPhone,proAddress,proFax,createdBy,creationDate) values(?,?,?,?,?,?,?,?,?)";
            Object[] params = {provider.getProCode(),provider.getProName(),provider.getProDesc(),provider.getProContact(),provider.getProPhone(),provider.getProAddress(),provider.getProFax(),provider.getCreatedBy(),provider.getCreationDate()};
            result = DBUtil.execute(connection, preparedStatement, sql, params);
            DBUtil.closeResource(preparedStatement, null);
        }
        return result;
    }

    /**
     * 条件查询结果集
     * @param connection 数据库连接
     * @param proName 供应商名
     * @param proCode 供应商编码
     * @return 供应商集合
     * @throws Exception 抛出SQL异常
     */
    @Override
    public List<Provider> getProviderList(Connection connection, String proName, String proCode) throws Exception {
        List<Provider> providers = new ArrayList<>();
        if (connection == null) {
            return providers;
        }
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        StringBuilder sql = new StringBuilder();
        sql.append("select * from smbms_provider");
        List<Object> params = new ArrayList<>();
        if (StringUtils.isNullOrEmpty(proCode)) {
            if (!StringUtils.isNullOrEmpty(proName)) {
                sql.append(" where proName like ?");
                params.add("%" + proName + "%");
            }
        } else {
            sql.append(" where proCode = ?");
            params.add(proCode);
            if (!StringUtils.isNullOrEmpty(proName)) {
                sql.append(" and proName like ?");
                params.add("%" + proName + "%");
            }
        }
        rs = DBUtil.query(connection, preparedStatement, rs, sql.toString(), params.toArray());
        while (rs.next()) {
            providers.add(getProviderByResult(rs));
        }
        DBUtil.closeResource(preparedStatement, rs);
        return providers;
    }

    /**
     * 条件查询总数量
     * @param connection 数据库连接
     * @param proName 供应商名
     * @param proCode 供应商编码
     * @return 数量
     * @throws Exception 抛出SQL异常
     */
    @Override
    public int getProviderCount(Connection connection, String proName, String proCode) throws Exception {
        int count = 0;
        if (connection == null) {
            return count;
        }
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        StringBuilder sql = new StringBuilder();
        sql.append("select count(1) as count from smbms_provider");
        List<Object> params = new ArrayList<>();
        if (StringUtils.isNullOrEmpty(proCode)) {
            if (!StringUtils.isNullOrEmpty(proName)) {
                sql.append(" where proName = ?");
                params.add("%" + proName + "%");
            }
        } else {
            sql.append(" where proCode = ?");
            params.add(proCode);
            if (!StringUtils.isNullOrEmpty(proName)) {
                sql.append(" and proName like ?");
                params.add("%" + proName + "%");
            }
        }
        rs = DBUtil.query(connection, preparedStatement, rs, sql.toString(), params.toArray());
        if (rs.next()) {
            count = rs.getInt("count");
        }
        DBUtil.closeResource(preparedStatement, rs);
        return count;
    }

    /**
     * 根据id获取供应商对象
     * @param connection 数据库连接
     * @param id 主键
     * @return 供应商对象
     * @throws Exception 抛出SQL异常
     */
    @Override
    public Provider getProviderById(Connection connection, int id) throws Exception {
        Provider provider = null;
        if (connection != null && id > 0) {
            PreparedStatement preparedStatement = null;
            ResultSet rs = null;
            String sql = "select * from smbms_provider where id = ?";
            Object[] params = {id};
            rs = DBUtil.query(connection, preparedStatement, rs, sql, params);
            if (rs.next()) {
                provider = getProviderByResult(rs);
            }
            DBUtil.closeResource(preparedStatement, rs);
        }
        return provider;
    }

    /**
     * 修改供应商信息
     * @param connection 数据库连接
     * @param provider 供应商对象
     * @return 结果数量
     * @throws Exception 抛出SQL异常
     */
    @Override
    public int modify(Connection connection, Provider provider) throws Exception {
        int result = 0;
        if (connection != null && provider != null) {
            PreparedStatement preparedStatement = null;
            String sql = "update smbms_provider set proName=?,proDesc=?,proContact=?,proPhone=?,proAddress=?,proFax=?,modifyBy=?,modifyDate=? where id = ?";
            Object[] params = {provider.getProName(),provider.getProDesc(),provider.getProContact(),provider.getProPhone(),provider.getProAddress(), provider.getProFax(),provider.getModifyBy(),provider.getModifyDate(),provider.getId()};
            result = DBUtil.execute(connection, preparedStatement, sql, params);
            DBUtil.closeResource(preparedStatement, null);
        }
        return result;
    }

    /**
     * 根据id删除供应商
     * @param connection 数据库连接
     * @param id 主键
     * @return 结果数量
     * @throws Exception 抛出SQL异常
     */
    @Override
    public int deleteProviderById(Connection connection, int id) throws Exception {
        int result = 0;
        if (connection != null && id > 0) {
            PreparedStatement preparedStatement = null;
            String sql = "delete from smbms_provider where id = ?";
            Object[] params = {id};
            result = DBUtil.execute(connection, preparedStatement, sql, params);
            DBUtil.closeResource(preparedStatement, null);
        }
        return result;
    }

    /**
     * 通过查询结果获取供应商对象
     * @param rs 查询结果
     * @return 供应商对象
     * @throws Exception 抛出SQL异常
     */
    private Provider getProviderByResult(ResultSet rs) throws Exception{
        Provider provider = new Provider();
        provider.setId(rs.getInt("id"));
        provider.setProCode(rs.getString("proCode"));
        provider.setProName(rs.getString("proName"));
        provider.setProDesc(rs.getString("proDesc"));
        provider.setProContact(rs.getString("proContact"));
        provider.setProPhone(rs.getString("proPhone"));
        provider.setProAddress(rs.getString("proAddress"));
        provider.setProFax(rs.getString("proFax"));
        provider.setCreatedBy(rs.getInt("createdBy"));
        provider.setCreationDate(rs.getDate("creationDate"));
        provider.setModifyBy(rs.getInt("modifyBy"));
        provider.setModifyDate(rs.getDate("modifyDate"));
        return provider;
    }
}
